using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 退费任务工作量服务实现
    /// </summary>
    public class RefundTaskWorkloadService : IRefundTaskWorkloadService
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="oracleConnectionFactory">Oracle连接工厂</param>
        public RefundTaskWorkloadService(IOracleConnectionFactory oracleConnectionFactory)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
        }

        /// <summary>
        /// 获取所有用户的实时退费任务工作量数据
        /// </summary>
        /// <returns>退费任务工作量数据列表</returns>
        public async Task<List<RefundTaskWorkload>> GetAllUserWorkloadAsync()
        {
            var result = new List<RefundTaskWorkload>();

            using var connection = (OracleConnection)_oracleConnectionFactory.CreateConnection();
            await connection.OpenAsync();
            using var command = (OracleCommand)connection.CreateCommand();

            command.CommandText = @"
                SELECT 
                    t1.people_name as fullname,
                    t1.user_name as username,
                    t1.task_amount,
                    (SELECT COUNT(tb2.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                      WHERE tb1.username = t1.user_name) as taskreceived,
                    (SELECT COUNT(tb2.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                      WHERE tb1.username = t1.user_name
                        AND tb2.contact_count > 0) as resultrecorded,
                    (SELECT COUNT(tb3.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                       JOIN pca.lzfy_refund_task_op_logs tb3
                         ON tb2.id = tb3.task_detail_id
                        AND tb3.actionid IS NOT NULL
                      WHERE tb1.username = t1.user_name) as callamount
                  FROM uum.uum_user t1
                 WHERE t1.task_amount > 0
                 ORDER BY t1.task_amount DESC";

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                result.Add(new RefundTaskWorkload
                {
                    username = reader.GetString("username"),
                    fullName = reader.GetString("fullname"),
                    TaskAmount = reader.GetInt32("task_amount"),
                    TaskReceived = reader.GetInt32("taskreceived"),
                    ResultRecorded = reader.GetInt32("resultrecorded"),
                    CallAmount = reader.GetInt32("callamount")
                });
            }

            return result;
        }

        /// <summary>
        /// 获取指定用户的实时退费任务工作量数据
        /// </summary>
        /// <param name="username">用户名</param>
        /// <returns>退费任务工作量数据</returns>
        public async Task<RefundTaskWorkload?> GetUserWorkloadAsync(string username)
        {
            using var connection = (OracleConnection)_oracleConnectionFactory.CreateConnection();
            await connection.OpenAsync();
            using var command = (OracleCommand)connection.CreateCommand();

            command.CommandText = @"
                SELECT 
                    t1.people_name as fullname,
                    t1.user_name as username,
                    t1.task_amount,
                    (SELECT COUNT(tb2.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                      WHERE tb1.username = t1.user_name) as taskreceived,
                    (SELECT COUNT(tb2.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                      WHERE tb1.username = t1.user_name
                        AND tb2.contact_count > 0) as resultrecorded,
                    (SELECT COUNT(tb3.id)
                       FROM pca.lzfy_refund_task_master tb1
                       JOIN pca.lzfy_refund_task_detail tb2
                         ON tb1.id = tb2.master_id
                       JOIN pca.lzfy_refund_task_op_logs tb3
                         ON tb2.id = tb3.task_detail_id
                        AND tb3.actionid IS NOT NULL
                      WHERE tb1.username = t1.user_name) as callamount
                  FROM uum.uum_user t1
                 WHERE t1.user_name = :username
                   AND t1.task_amount > 0";

            command.Parameters.Add(new OracleParameter("username", username));

            using var reader = await command.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                return new RefundTaskWorkload
                {
                    username = reader.GetString("username"),
                    fullName = reader.GetString("fullname"),
                    TaskAmount = reader.GetInt32("task_amount"),
                    TaskReceived = reader.GetInt32("taskreceived"),
                    ResultRecorded = reader.GetInt32("resultrecorded"),
                    CallAmount = reader.GetInt32("callamount")
                };
            }

            return null;
        }

        /// <summary>
        /// 获取退费任务工作量排名前N的用户
        /// </summary>
        /// <param name="topN">前N名</param>
        /// <returns>退费任务工作量数据列表</returns>
        public async Task<List<RefundTaskWorkload>> GetTopWorkloadUsersAsync(int topN)
        {
            var result = new List<RefundTaskWorkload>();

            using var connection = (OracleConnection)_oracleConnectionFactory.CreateConnection();
            await connection.OpenAsync();
            using var command = (OracleCommand)connection.CreateCommand();

            command.CommandText = @"
                SELECT * FROM (
                    SELECT 
                        t1.people_name as fullname,
                        t1.user_name as username,
                        t1.task_amount,
                        (SELECT COUNT(tb2.id)
                           FROM pca.lzfy_refund_task_master tb1
                           JOIN pca.lzfy_refund_task_detail tb2
                             ON tb1.id = tb2.master_id
                          WHERE tb1.username = t1.user_name) as taskreceived,
                        (SELECT COUNT(tb2.id)
                           FROM pca.lzfy_refund_task_master tb1
                           JOIN pca.lzfy_refund_task_detail tb2
                             ON tb1.id = tb2.master_id
                          WHERE tb1.username = t1.user_name
                            AND tb2.contact_count > 0) as resultrecorded,
                        (SELECT COUNT(tb3.id)
                           FROM pca.lzfy_refund_task_master tb1
                           JOIN pca.lzfy_refund_task_detail tb2
                             ON tb1.id = tb2.master_id
                           JOIN pca.lzfy_refund_task_op_logs tb3
                             ON tb2.id = tb3.task_detail_id
                            AND tb3.actionid IS NOT NULL
                          WHERE tb1.username = t1.user_name) as callamount,
                        ROWNUM as rn
                    FROM uum.uum_user t1
                    WHERE t1.task_amount > 0
                    ORDER BY t1.task_amount DESC
                )
                WHERE rn <= :topN
                ORDER BY task_amount DESC";

            command.Parameters.Add(new OracleParameter("topN", topN));

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                result.Add(new RefundTaskWorkload
                {
                    username = reader.GetString("username"),
                    fullName = reader.GetString("fullname"),
                    TaskAmount = reader.GetInt32("task_amount"),
                    TaskReceived = reader.GetInt32("taskreceived"),
                    ResultRecorded = reader.GetInt32("resultrecorded"),
                    CallAmount = reader.GetInt32("callamount")
                });
            }

            return result;
        }
    }
}